from django.db import models
from django.db import connection


class ProjectUserDataFields:
    project_name = ' '
    project_id = -1
    user_name = ' '
    user_id = -1
    
    def __init__(self, sql_row):
        self.project_name = sql_row[0]
        self.project_id = sql_row[1]
        self.user_name = sql_row[2]
        self.user_id = sql_row[3]
    
    
class UserDataFields:
    user_name = ' '
    user_id = -1
    
    def __init__(self, sql_row):
        self.user_name = sql_row[0]
        self.user_id = sql_row[1]
        
        
class PMUDataManager(models.Manager):
    def get_query_set(self):
        return super(PMUDataManager, self).get_query_set()
        
    def project_users(self, pm_id):
        cursor = connection.cursor()
        cursor.execute("""
                        SELECT p.name_project, p.id, u.username, u.id
                        FROM timesheet_project p, auth_user u, manager_projectmanageruser pmu,
                            manager_projectmanager pm
                        WHERE p.id=pmu.project_id AND
                            u.id=pmu.his_user_id AND
                            pmu.manager_id=pm.id AND
                            pm.manager_id=%s
                        ORDER BY pmu.project_id;
                        """, [pm_id])
        result_list = []
        for row in cursor.fetchall():
            elem = ProjectUserDataFields(row)
            result_list.append(elem)
        return result_list

    def take_all_users(self):
        cursor = connection.cursor()
        cursor.execute("""
                        SELECT u.username, u.id
                        FROM auth_user u
                        """)
        result_list = []
        for row in cursor.fetchall():
            elem = UserDataFields(row)
            result_list.append(elem)
        return result_list
        
    def take_his_project_users(self, pm_id):
        cursor = connection.cursor()
        cursor.execute("""
                        SELECT p.name_project, p.id, u.username, u.id
                        FROM timesheet_project p, auth_user u, manager_projectmanageruser pmu, 
                            manager_projectmanager pm
                        WHERE p.id=pmu.project_id AND
                            u.id=pmu.his_user_id AND
                            pmu.manager_id=pm.id AND
                            pm.manager_id=%s
                        """, [pm_id])
        result_list = []
        for row in cursor.fetchall():
            elem = ProjectUserDataFields(row)
            result_list.append(elem)
        return result_list
        
    def take_all_project_users(self, pm_id):
        cursor = connection.cursor()
        cursor.execute("""
                        SELECT p.name_project, p.id, u.username, u.id
                        FROM timesheet_project p, auth_user u, manager_projectmanageruser pmu
                        WHERE u.id=pmu.his_user_id AND
                            p.id=pmu.project_id AND
                            pmu.project_id IN 
                            (   SELECT p.id
                                FROM timesheet_project p, manager_projectmanageruser pmu,
                                    manager_projectmanager pm
                                WHERE p.id=pmu.project_id AND
                                    pmu.manager_id=pm.id AND
                                    pm.manager_id=%s
                            )
                        """, [pm_id])
        result_list = []
        for row in cursor.fetchall():
            elem = ProjectUserDataFields(row)
            result_list.append(elem)
        return result_list
        
        
